#!/bin/bash
#description: Use mysqldump to backup mysql.
# GRANT SELECT, RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup'@'localhost'
#编写my.cnf文件,添加
#[client]
#user = backup
#password = Aa123321

#定义变量
HOST="localhost"
DATE="$(date +%F_%H)"
MYCMD="/usr/local/mysql/bin/mysql  --defaults-extra-file=/etc/my.cnf"
BACKUPDIR="/opt/backup/database"
MYDUMP="/usr/local/mysql/bin/mysqldump  --defaults-extra-file=/etc/my.cnf  -x --master-data=2"
DBLIST=`$MYCMD -e "show databases;"|sed 1d|egrep -v "_schema|test"`
#判断备份目录是否存在
[ ! -d ${BACKUPDIR}/${DATE} ] && mkdir -p ${BACKUPDIR}/${DATE}
#刷新生成二进制日志
$MYCMD -e "flush logs;"
#循环备份数据库
for dbname in $DBLIST;do
   TLIST=`$MYCMD -e "show tables from $dbname;"|sed 1d`
   for tname in $TLIST;do
        mkdir -p $BACKUPDIR/${DATE}/$dbname
        $MYDUMP $dbname $tname|gzip >${BACKUPDIR}/${DATE}/${dbname}/${dbname}_${tname}_${DATE}.sql.gz
   done
done

